#!/usr/bin/python
# -*- coding: utf-8 -*-

import codecs
import re
import string
import sys
from pepe.p_sqlpg.pg import Pg

n = '\x0D\x0A'

def group_by_pg(table, column, limit):
    ''' '''
    if limit == 0: lmt = ''
    else: lmt=' limit %s' % (str(limit),)
    
    p = Pg(); p.connect()

    q = '''
    select a.%s, count(1) 
      from (select * from %s%s) as a
     group by a.%s
    ''' % (column, table, lmt, column)

    p.query(q)
    
    res = []
    for row in p:
        rrow = []
        for strc in [str(c) for c in row]:
            rrow.append('\t%s' % (strc, ))
        res.append(rrow[:])

    return res


class ExamineColumn:
    ''' examine database column class
    '''
    def __init__(self):
        ''' '''
        self.db_type = 'pgsql'
        # table and column to be scanned
        self.table = ''
        self.column = ''
        self.column_name = ''
        self.column_sample = ''

        # how deeply database are to be scanned, default unlimited
        self.limit_rows = 0
        # treshold for unique values for 'category' column
        self.limit_category = 20
        self.cnt_uniq_all = True
        
        self.empty = False
        self.category = False
        self.continuous = False

    def is_category(self):
        ''' '''
        # try first on small sample
        if self.db_type == 'pgsql':
            r = group_by_pg(  self.table
                            , self.column
                            , limit = 1000)
            if len(r) <= self.limit_category:
                # if above True, go deep as self.limit_rows
                r = group_by_pg(  self.table
                                , self.column
                                , limit = self.limit_rows)
                if len(r) <= self.limit_category:
                    self.category = True
                    return True
            else:
                return False

    def is_empty(self):
        ''' '''
        # try first on small sample
        if self.db_type == 'pgsql':
            r = group_by_pg(  self.table
                            , self.column
                            , limit = 1000)
            #print r[0][0]
            if len(r) == 1 and r[0][0] in ('\t.', '\t', '\t'):
                # if above True, go deep as self.limit_rows
                r = group_by_pg(  self.table
                                , self.column
                                , limit = self.limit_rows)
                #print r[0][0]
                if len(r) == 1 and r[0][0] in ('\t.', '\t', '\t'):
                    self.empty = True
                    return True
            else:
                return False

    def run_pprint(self):
        ''' '''
        if self.db_type == 'pgsql':
            if self.cnt_uniq_all:
                r = group_by_pg(  self.table
                                , self.column
                                , self.limit_rows)
                cnt_uniq = str(len(r))
            elif self.category: 
                # otherwise calculate only for category columns
                r = group_by_pg(  self.table
                                , self.column
                                , self.limit_rows)
                cnt_uniq = str(len(r))
            else:
                cnt_uniq = 'unknown'

        # output column type
        if self.empty:
            print '%s\t%s\t%s\t%s\t%s' % (self.table, self.column
                                , self.column_name, self.column_sample
                                , 'IS_EMPTY')
        elif self.category:
            print '%s\t%s\t%s\t%s\t%s' % (self.table, self.column
                                , self.column_name, self.column_sample
                                , 'IS_CATEGORY')
            print '%s\t%s\t%s\t%s\t%s\t\t%s' % (self.table, self.column
                                , self.column_name, self.column_sample
                                , 'CNT_UNIQ', cnt_uniq)
        else:
            print '%s\t%s\t%s\t%s\t%s' % (self.table, self.column
                                , self.column_name, self.column_sample
                                , 'IS_CONTINUOUS')
            print '%s\t%s\t%s\t%s\t%s\t\t%s' % (self.table, self.column
                                , self.column_name, self.column_sample
                                , 'CNT_UNIQ', cnt_uniq)

        # (if category column type) output column values
        if self.category:
            out = ''
            for row in r:
                out += '%s\t%s\t%s\t%s\t\t' % (self.table, self.column
                                , self.column_name, self.column_sample)
                for sc in [str(c) for c in row]:
                    out += "'%s\t" % (re.sub('\t', '', sc), )
                print out
                out = ''

    def run_tofile(self, o):
        ''' '''
        if self.db_type == 'pgsql':
            if self.cnt_uniq_all:
                r = group_by_pg(  self.table
                                , self.column
                                , self.limit_rows)
                cnt_uniq = str(len(r))
            elif self.category: 
                # otherwise calculate only for category columns
                r = group_by_pg(  self.table
                                , self.column
                                , self.limit_rows)
                cnt_uniq = str(len(r))
            else:
                cnt_uniq = 'unknown'

        # output column type
        if self.empty:
            o.write('%s\t%s\t%s\t%s\t%s' % (self.table, self.column
                                , self.column_name, self.column_sample
                                , 'IS_EMPTY') + n)
        elif self.category:
            o.write('%s\t%s\t%s\t%s\t%s' % (self.table, self.column
                                , self.column_name, self.column_sample
                                , 'IS_CATEGORY') + n)
            o.write('%s\t%s\t%s\t%s\t%s\t\t%s' % (self.table, self.column
                                , self.column_name, self.column_sample
                                , 'CNT_UNIQ', cnt_uniq) + n)
        else:
            o.write('%s\t%s\t%s\t%s\t%s' % (self.table, self.column
                                , self.column_name, self.column_sample
                                , 'IS_CONTINUOUS') + n)
            o.write('%s\t%s\t%s\t%s\t%s\t\t%s' % (self.table, self.column
                                , self.column_name, self.column_sample
                                , 'CNT_UNIQ', cnt_uniq) + n)

        # (if category column type) output column values
        if self.category:
            out = ''
            for row in r:
                out += '%s\t%s\t%s\t%s\t\t' % (self.table, self.column
                                , self.column_name, self.column_sample)
                for sc in [str(c) for c in row]:
                    out += "'%s\t" % (re.sub('\t', '', sc), )
                o.write(out + n)
                out = ''

def per_input_file(f):
    ''' č
    '''
    #lines = codecs.open(f, 'rb', 'utf-8').readlines()
    lines = open(f, 'rb').readlines()

    print 'file_table	column	column_orig_name	column_sample	values_type	value	count'
    #o = codecs.open(f + '_examine_columns.#', 'wb', 'utf-8')
    #o.write('dšfile_table	column	column_orig_name	column_sample	values_type	value	count' + n)

    for row in lines:
        if len(row) > 2:
            row = re.sub('\x0D\x0A', '', row)
            
            tb, co, cn, cs = string.split(row, '\t')

            e = ExamineColumn()
            #e.table = 'quantum.aaa_POC_INPUT_APS'
            #e.column = 'c007'
            
            #print cn, cs
            
            e.table = tb
            e.column = co
            e.column_name = cn
            e.column_sample = cs

            e.limit_rows = 0 #10000
            e.limit_category = 100
            #e.cnt_uniq_all = False
            e.is_empty()
            e.is_category()

            e.run_pprint()
            #e.run_tofile(o)
    #o.close()

if __name__ == '__main__':
    ''' '''
    per_input_file(sys.argv[1])
